/**
 * www.easyplatform.cn ©2016
 */
package cn.easyplatform.studio.dao;

import cn.easyplatform.entities.beans.table.TableField;
import cn.easyplatform.lang.Nums;
import cn.easyplatform.studio.vos.CreateTableVo;
import cn.easyplatform.studio.vos.FieldVo;
import cn.easyplatform.type.FieldType;
import cn.easyplatform.utils.SerializationUtils;
import org.zkoss.util.resource.Labels;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author <a href="mailto:shiny_vc@163.com">陈云亮</a> <br/>
 * @since 2.0.0 <br/>
 */
public final class DaoUtils {

    /**
     * SQL SERVER
     */
    public static final int DATETIMEOFFSET = -155;

    public static final int DB2 = 1;

    public static final int ORACLE = 2;

    public static final int MYSQL = 3;

    public static final int SQLSERVER = 4;

    public static final int DERBY = 5;

    public static final int PSQL = 6;

    public static final int SYBASE = 7;

    /**
     * @param jdbcUrl
     * @return
     */
    public static int getDbType(String jdbcUrl) {
        String url = jdbcUrl.toLowerCase();
        url = url.substring(5);
        if (url.startsWith("oracle"))
            return ORACLE;
        if (url.startsWith("mysql"))
            return MYSQL;
        if (url.startsWith("sqlserver"))
            return SQLSERVER;
        if (url.startsWith("derby"))
            return DERBY;
        if (url.startsWith("db2"))
            return DB2;
        if (url.startsWith("postgresql"))
            return PSQL;
        if (url.startsWith("sybase"))
            return SYBASE;
        return 0;
    }

    /**
     * @param conn
     * @throws SQLException
     */
    public static void close(Connection conn) throws SQLException {
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * @param rs
     * @throws SQLException
     */
    public static void close(ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
            rs = null;
        }
    }

    /**
     * @param stmt
     * @throws SQLException
     */
    public static void close(Statement stmt) throws SQLException {
        if (stmt != null) {
            stmt.close();
            stmt = null;
        }
    }

    /**
     * @param conn
     */
    public static void closeQuietly(Connection conn) {
        try {
            close(conn);
        } catch (SQLException e) {
            // quiet
        }
    }

    /**
     * @param stmt
     * @param rs
     */
    public static void closeQuietly(Statement stmt, ResultSet rs) {
        closeQuietly(rs);
        closeQuietly(stmt);
    }

    /**
     * @param rs
     */
    public static void closeQuietly(ResultSet rs) {
        try {
            close(rs);
        } catch (SQLException e) {
            // quiet
        }
    }

    /**
     * @param stmt
     */
    public static void closeQuietly(Statement stmt) {
        try {
            close(stmt);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void setValue(PreparedStatement pstmt, int index, FieldVo fd) {
        try {
            switch (fd.getType()) {
                case CHAR:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.CHAR);
                    else
                        pstmt.setString(index, fd.getValue().toString());
                    break;
                case VARCHAR:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.VARCHAR);
                    else
                        pstmt.setString(index, fd.getValue().toString());
                    break;
                case CLOB:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.CLOB);
                    else
                        pstmt.setString(index, (String) fd.getValue());
                    break;
                case LONG:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.BIGINT);
                    else if (fd.getValue() instanceof Long)
                        pstmt.setLong(index, (Long) fd.getValue());
                    else if (fd.getValue() instanceof Integer)
                        pstmt.setInt(index, (Integer) fd.getValue());
                    else if (fd.getValue() instanceof Short)
                        pstmt.setShort(index, (Short) fd.getValue());
                    else if (fd.getValue() instanceof Byte)
                        pstmt.setByte(index, (Byte) fd.getValue());
                    else if (fd.getValue() instanceof Number)
                        pstmt.setLong(index, ((Number) fd.getValue()).longValue());
                    else
                        pstmt.setLong(index,
                                Nums.toLong(fd.getValue().toString(), 0));
                    break;
                case INT:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.INTEGER);
                    else if (fd.getValue() instanceof Long)
                        pstmt.setLong(index, (Long) fd.getValue());
                    else if (fd.getValue() instanceof Integer)
                        pstmt.setInt(index, (Integer) fd.getValue());
                    else if (fd.getValue() instanceof Short)
                        pstmt.setShort(index, (Short) fd.getValue());
                    else if (fd.getValue() instanceof Byte)
                        pstmt.setByte(index, (Byte) fd.getValue());
                    else if (fd.getValue() instanceof Number)
                        pstmt.setInt(index, ((Number) fd.getValue()).intValue());
                    else if (fd.getValue() instanceof Boolean)
                        pstmt.setInt(index, ((Boolean) fd.getValue()) ? 1 : 0);
                    else
                        pstmt.setInt(index, Nums.toInt(fd.getValue().toString(), 0));
                    break;
                case NUMERIC:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.NUMERIC);
                    else if (fd.getValue() instanceof Float)
                        pstmt.setFloat(index, (Float) fd.getValue());
                    else if (fd.getValue() instanceof Double)
                        pstmt.setDouble(index, (Double) fd.getValue());
                    else if (fd.getValue() instanceof Boolean)
                        pstmt.setInt(index, ((Boolean) fd.getValue()) ? 1 : 0);
                    else
                        pstmt.setBigDecimal(index, new BigDecimal(fd.getValue()
                                .toString()));
                    break;
                case TIME:
                    if (fd.getValue() != null)
                        pstmt.setTime(index,
                                new Time(((Date) fd.getValue()).getTime()));
                    else
                        pstmt.setNull(index, Types.TIME);
                    break;
                case DATE:
                    if (fd.getValue() != null)
                        pstmt.setDate(index,
                                new java.sql.Date(((Date) fd.getValue()).getTime()));
                    else
                        pstmt.setNull(index, Types.DATE);
                    break;
                case DATETIME:
                    if (fd.getValue() != null)
                        pstmt.setTimestamp(index,
                                new Timestamp(((Date) fd.getValue()).getTime()));
                    else
                        pstmt.setNull(index, Types.TIMESTAMP);
                    break;
                case BLOB:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.BLOB);
                    else
                        pstmt.setBytes(index, (byte[]) fd.getValue());
                    break;
                case BOOLEAN:
                    if (fd.getValue() == null)
                        pstmt.setInt(index, 0);
                    else if (fd.getValue() instanceof Boolean) {
                        Boolean b = (Boolean) fd.getValue();
                        pstmt.setInt(index, b ? 1 : 0);
                    } else if (fd.getValue() instanceof Number) {
                        pstmt.setInt(index,
                                ((Number) fd.getValue()).intValue() > 0 ? 1 : 0);
                    } else {
                        String s = fd.getValue().toString();
                        pstmt.setInt(index, s.equalsIgnoreCase("true") ? 1 : 0);
                    }
                    break;
                case OBJECT:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.BLOB);
                    else
                        pstmt.setBytes(index,
                                SerializationUtils.serialize(fd.getValue()));
                    break;
                default:
                    throw new DaoException(Labels.getLabel("",
                            new Object[]{fd.getType()}));
            }
        } catch (Exception ex) {
            throw new DaoException("setValue", ex);
        }
    }

    public static Object getValue(ResultSet rs, ResultSetMetaData rsmd,
                                  int index) throws SQLException {
        String name = null;
        if (rsmd.getColumnLabel(index) != null)
            name = rsmd.getColumnLabel(index);
        else
            name = rsmd.getColumnName(index);
        FieldType type = getType(name, rsmd.getColumnType(index));
        switch (type) {
            case CHAR:
            case VARCHAR:
            case CLOB:
                return rs.getString(index);
            case NUMERIC:
                Object v = rs.getObject(index);
                if (v instanceof BigDecimal) {
                    BigDecimal bd = (BigDecimal) v;
                    v = bd.doubleValue();
                }
                return v;
            case LONG:
                return rs.getLong(index);
            case INT:
                return rs.getInt(index);
            case TIME:
                return rs.getTime(index);
            case DATE:
                return rs.getDate(index);
            case DATETIME:
                return rs.getTimestamp(index);
            case BLOB:
                return rs.getBytes(index);
            case BOOLEAN:
                return rs.getBoolean(index);
            case OBJECT:
                byte[] bytes = rs.getBytes(index);
                if (bytes != null)
                    return SerializationUtils.deserialize(bytes);
                return null;
            default:
                return rs.getObject(index);
        }
    }

    public static String getTypeName(FieldType type) {
        String string = null;
        switch (type) {
            case VARCHAR:
                string = "varchar";
                break;
            case BOOLEAN:
                string = "boolean";
                break;
            case CHAR:
                string = "char";
                break;
            case CLOB:
                string = "clob";
                break;
            case BLOB:
                string = "blob";
                break;
            case DATETIME:
                string = "dateTime";
                break;
            case DATE:
                string = "date";
                break;
            case TIME:
                string = "time";
                break;
            case INT:
                string = "int";
                break;
            case LONG:
                string = "long";
                break;
            case NUMERIC:
                string = "numeric";
                break;
            case OBJECT:
                string = "object";
                break;
        }
        return string;
    }

    public static FieldType getType(String name, int sqlType) {
        switch (sqlType) {
            case Types.BIT:
            case Types.TINYINT:
            case Types.INTEGER:
            case Types.SMALLINT:
                return FieldType.INT;
            case Types.BIGINT:
                return FieldType.LONG;
            case Types.CLOB:
            case Types.LONGNVARCHAR:
            case Types.LONGVARCHAR:
            case Types.SQLXML:
                return FieldType.CLOB;
            case Types.VARCHAR:
            case Types.NCHAR:
            case Types.NCLOB:
            case Types.NVARCHAR:
            case Types.ROWID:
                return FieldType.VARCHAR;
            case Types.CHAR:
                return FieldType.CHAR;
            case Types.BOOLEAN:
                return FieldType.BOOLEAN;
            case Types.BLOB:
            case Types.LONGVARBINARY:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.STRUCT:
                return FieldType.BLOB;
            case Types.JAVA_OBJECT:
                return FieldType.OBJECT;
            case Types.DATE:
                return FieldType.DATE;
            case Types.TIME:
                return FieldType.TIME;
            case Types.TIMESTAMP:
            case DATETIMEOFFSET:
                return FieldType.DATETIME;
            case Types.NUMERIC:
            case Types.DOUBLE:
            case Types.FLOAT:
            case Types.REAL:
            case Types.DECIMAL:
                return FieldType.NUMERIC;
        }
        throw new DaoException(String.format(
                "Column %s unsupported jdbc type:%s", name, sqlType));
    }

    public static StringBuilder createTable(CreateTableVo tableVo, Dialect dialect) {
        StringBuilder sb = new StringBuilder("CREATE TABLE ").append(tableVo.getTableName())
                .append(" (");
        List<String> keys = new ArrayList<>();
        if (tableVo.getTfs().size() > 0) {
            for (CreateTableVo.TableField tableField: tableVo.getTfs()) {
                sb.append(tableField.getTfName()).append(" ");
                TableField tf = new TableField();
                tf.setType(tableField.getType());
                if (tableField.getLength() != 0)
                    tf.setLength(tableField.getLength());
                sb.append(dialect.evalFieldType(tf));
                if (tableField.isNull() == false)
                    sb.append(" NOT NULL");
                if (tableField.isAutoIncrement() == true)
                    sb.append(" AUTO_INCREMENT");
                if (tableField.isDefaultCurrentTime() == true)
                    sb.append(" DEFAULT CURRENT_TIMESTAMP");
                if (tableField.isUpdateCurrentTime() == true) {
                    if (tableField.isNull() == true)
                        sb.append(" NULL");
                    sb.append(" ON UPDATE CURRENT_TIMESTAMP");
                }
                sb.append(",");
                if (tableField.isKey() == true)
                    keys.add(tableField.getTfName());
            }
        }
        if (keys.size() > 0) {
            sb.append("PRIMARY KEY (");
            for (String name : keys) {
                sb.append(name).append(",");
            }
            sb.delete(sb.length() - 1, sb.length());
        }
        sb.append("))");
        return sb;
    }
}
